% cohort method to statistics on quarter
clear all;
clc;
freqj=4;% 4 quarters
year_list=[1997:2010]';
year_list_text=num2str(year_list);
quarter_list=[1:4]';
quarter_list_text=num2str(quarter_list);
Rate_list=[0,1:0.5:5];
final_result=zeros(13,freqj,10,13); % 1-10 column is rating for 0-5, column 11 is default, column 12 is dwithdraw, column 13 is total

for i=2:2 % i=1:13 number of year
     %for iijj=1:freqj
     for iijj=1:1
        if iijj==freqj
            for j=1:length(Rate_list) % number of diff rating 0-5
                
                db1_open = database('TP06_Q_RJ_final','', ''); %open the connection to the database
                %db1_open = database('TP09_Q_final','', ''); %open the connection to the database Less than 100k 
                %sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number] FROM TP06_Q_RJ_final AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month])) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number],qwry1.[Quarter] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
                sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number] FROM TP06_Q_RJ_final AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.quarter)=',num2str(quarter_list_text(iijj,:)),') AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month])) GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number],qwry1.[Quarter] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
                
                curs = exec(db1_open, sqlquery1);
                curs = fetch(curs);
                db1_cell=curs.Data;
                close (db1_open);
                if strcmp(db1_cell,'No Data')
                    disp('check database');
                    i
                    j
                    iijj
                    %final_result=zeros(j,:)=0;
                else
                    custom_number_list=cell2mat(db1_cell(:,3));
                    [total_num,col]=size(custom_number_list); % total_num is the number of companies from last year
                    final_result(i,iijj,j,13)=total_num;
                    clear curs sqlquery1 db1_cell custom_number_list db1_open;
                    
                    
                    db1_open = database('TP06_Q_RJ_final','', ''); %open the connection to the database
                    sqlquery2 = strcat('SELECT [TP06_Q_RJ_final].[Overall Risk Rating (Hist)] AS Expr1, Count([TP06_Q_RJ_final].[Customer Number]) AS NumCN FROM TP06_Q_RJ_final, (SELECT [TP06_Q_RJ_final].[Customer Number] FROM TP06_Q_RJ_final WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Q_RJ_final.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Q_RJ_final.Status) Is Null))) AS q1 WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i+1,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(1,:)),') And ((TP06_Q_RJ_final.Status) Is Null)) And ((TP06_Q_RJ_final.[Customer Number])=q1.[Customer Number]) GROUP BY [TP06_Q_RJ_final].[Overall Risk Rating (Hist)]');
                    curs = exec(db1_open, sqlquery2);
                    curs = fetch(curs);
                    db2_cell=curs.Data;
                    close (db1_open);
                    clear curs;
                    stats_list=cell2mat(db2_cell);
                    for ii=1:length(stats_list)
                        switch stats_list(ii,1)
                            case 0
                                final_result(i,iijj,j,1) = stats_list(ii,2);
                            case 1
                                final_result(i,iijj,j,2) = stats_list(ii,2);
                            case 1.5
                                final_result(i,iijj,j,3) = stats_list(ii,2);
                            case 2
                                final_result(i,iijj,j,4) = stats_list(ii,2);
                            case 2.5
                                final_result(i,iijj,j,5) = stats_list(ii,2);
                            case 3
                                final_result(i,iijj,j,6) = stats_list(ii,2);
                            case 3.5
                                final_result(i,iijj,j,7) = stats_list(ii,2);
                            case 4
                                final_result(i,iijj,j,8) = stats_list(ii,2);
                            case 4.5
                                final_result(i,iijj,j,9) = stats_list(ii,2);
                            case 5
                                final_result(i,iijj,j,10) = stats_list(ii,2);
                            otherwise
                                disp('WTF!!!')
% %                                 overfivetemp = stats_list(ii,2);
                        end
                    end
                    clear curs sqlquery2 db2_cell stats_list db1_open;
                    
                    db1_open = database('TP06_Q_RJ_final','', ''); %open the connection to the database
                    %sqlquery3 = strcat('SELECT Count([TP06_Q_RJ_final].[Customer Number]) AS NumCN FROM TP06_Q_RJ_final, (SELECT [TP06_Q_RJ_final].[Customer Number] FROM TP06_Q_RJ_final WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Q_RJ_final.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Q_RJ_final.Status) Is Null))) AS q1 WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i+1,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(1,:)),') And ((TP06_Q_RJ_final.default)="default") And ((TP06_Q_RJ_final.[Customer Number])=q1.[Customer Number])');
                    sqlquery3 = strcat('SELECT Count(TP06_Q_RJ_final.[Customer Number]) AS NumCN FROM TP06_Q_RJ_final, (SELECT [TP06_Q_RJ_final].[Customer Number] FROM TP06_Q_RJ_final WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Q_RJ_final.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Q_RJ_final.Status) Is Null)))  AS q1 WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i+1,:),''') AND ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(1,:)),') AND ((TP06_Q_RJ_final.Default)="Default") AND ((TP06_Q_RJ_final.[Customer Number])=[q1].[Customer Number]))');
                    curs = exec(db1_open, sqlquery3);
                    curs = fetch(curs);
                    db3_cell=curs.Data;
                    close (db1_open);
                    clear curs;
                    if strcmp(db3_cell,'No Data')
                        final_result(i,iijj,j,11) = 0;
                    else
                        default_list=cell2mat(db3_cell);
                        final_result(i,iijj,j,11)= default_list(:,1);
                        %default_list=db3_cell;
                        %final_result(i,iijj,j,11) = default_list;
                    end
                    clear curs sqlquery3 db3_cell stats_list db1_open;
                    
                    final_result(i,iijj,j,12)=final_result(i,iijj,j,13)-sum(final_result(i,iijj,j,1:11));
                    
%                     clear withdrawtemp
                end
            end
            
        else
            for j=1:length(Rate_list); % number of diff rating 0-5
                
                db1_open = database('TP06_Q_RJ_final','', ''); %open the connection to the database
 %              sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number] FROM TP06_Q_RJ_final AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month]) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),') GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number],qwry1.[Quarter] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
                sqlquery1 = strcat('SELECT qwry1.Year, qwry1.[Overall Risk Rating (Hist)] AS RRwS, qwry1.[Customer Number] FROM TP06_Q_RJ_final AS qwry1, input_vars AS [input], [LifeCyclePD04*a-Last_Fiscal_Month] AS qwry2 WHERE ((([qwry1].[year]+1)<[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.Quarter)=',num2str(quarter_list_text(iijj,:)),')) OR ((([qwry1].[year]+1)=[qwry2].[max_fiscal_year]) AND ((qwry1.Status) Is Null) AND ((qwry1.Year) Is Not Null) AND ((qwry1.quarter)=',num2str(quarter_list_text(iijj,:)),') AND ((qwry2.Max_Fiscal_Month)>=[input].[calender_year_start_month])) GROUP BY qwry1.Year, qwry1.[Overall Risk Rating (Hist)], qwry1.[Customer Number], qwry1.[Quarter] HAVING (((qwry1.Year)=''',year_list_text(i,:),''') AND ((qwry1.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),'))');
                
                curs = exec(db1_open, sqlquery1);
                curs = fetch(curs);
                db1_cell=curs.Data;
                close (db1_open);
                if strcmp(db1_cell,'No Data')
                    disp('check database');
                    i
                    j
                    iijj
                    %final_result=zeros(j,:)=0;
                else
                    custom_number_list=cell2mat(db1_cell(:,3));
                    [total_num,col]=size(custom_number_list); % total_num is the number of companies from last year
                    final_result(i,iijj,j,13)=total_num;
                    clear curs sqlquery1 db1_cell custom_number_list db1_open;
                    
                    
                    db1_open = database('TP06_Q_RJ_final','', ''); %open the connection to the database
                    sqlquery2 = strcat('SELECT [TP06_Q_RJ_final].[Overall Risk Rating (Hist)] AS Expr1, Count([TP06_Q_RJ_final].[Customer Number]) AS NumCN FROM TP06_Q_RJ_final, (SELECT [TP06_Q_RJ_final].[Customer Number] FROM TP06_Q_RJ_final WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''')And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Q_RJ_final.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Q_RJ_final.Status) Is Null))) AS q1 WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj+1,:)),') And ((TP06_Q_RJ_final.Status) Is Null)) And ((TP06_Q_RJ_final.[Customer Number])=q1.[Customer Number]) GROUP BY [TP06_Q_RJ_final].[Overall Risk Rating (Hist)]');
                    %sqlquery2 = strcat('SELECT [TP06_Q_RJ_final].[Overall Risk Rating (Hist)] AS Expr1, Count([TP06_Q_RJ_final].[Customer Number]) AS NumCN FROM TP06_Q_RJ_final, (SELECT [TP06_Q_RJ_final].[Customer Number] FROM TP06_Q_RJ_final WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Q_RJ_final.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Q_RJ_final.Status) Is Null))) AS q1 WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i+1,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(1,:)),') And ((TP06_Q_RJ_final.Status) Is Null)) And ((TP06_Q_RJ_final.[Customer Number])=q1.[Customer Number]) GROUP BY [TP06_Q_RJ_final].[Overall Risk Rating (Hist)]');
                    curs = exec(db1_open, sqlquery2);
                    curs = fetch(curs);
                    db2_cell=curs.Data;
                    close (db1_open);
                    clear curs;
                    stats_list=cell2mat(db2_cell);
                    [mmx,nnx]=size(stats_list);
                    for ii=1:mmx
                        switch stats_list(ii,1)
                            case 0
                                final_result(i,iijj,j,1) = stats_list(ii,2);
                            case 1
                                final_result(i,iijj,j,2) = stats_list(ii,2);
                            case 1.5
                                final_result(i,iijj,j,3) = stats_list(ii,2);
                            case 2
                                final_result(i,iijj,j,4) = stats_list(ii,2);
                            case 2.5
                                final_result(i,iijj,j,5) = stats_list(ii,2);
                            case 3
                                final_result(i,iijj,j,6) = stats_list(ii,2);
                            case 3.5
                                final_result(i,iijj,j,7) = stats_list(ii,2);
                            case 4
                                final_result(i,iijj,j,8) = stats_list(ii,2);
                            case 4.5
                                final_result(i,iijj,j,9) = stats_list(ii,2);
                            case 5
                                final_result(i,iijj,j,10) = stats_list(ii,2);                            
                            otherwise
                                disp('WTF!!!')
                        end
                    end
                    clear curs sqlquery2 db2_cell stats_list db1_open;
                    
                    db1_open = database('TP06_Q_RJ_final','', ''); %open the connection to the database
                    %sqlquery3 = strcat('SELECT Count([TP06_Q_RJ_final].[Customer Number]) AS NumCN FROM TP06_Q_RJ_final, (SELECT [TP06_Q_RJ_final].[Customer Number] FROM TP06_Q_RJ_final WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Q_RJ_final.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Q_RJ_final.Status) Is Null))) AS q1 WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj+1,:)),') And ((TP06_Q_RJ_final.default)="default") And ((TP06_Q_RJ_final.[Customer Number])=q1.[Customer Number])');
                    sqlquery3 = strcat('SELECT Count(TP06_Q_RJ_final.[Customer Number]) AS NumCN FROM TP06_Q_RJ_final, (SELECT [TP06_Q_RJ_final].[Customer Number] FROM TP06_Q_RJ_final WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') And ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj,:)),') And ((TP06_Q_RJ_final.[Overall Risk Rating (Hist)])=',num2str(Rate_list(j)),') And ((TP06_Q_RJ_final.Status) Is Null)))  AS q1 WHERE (((TP06_Q_RJ_final.Year)=''',year_list_text(i,:),''') AND ((TP06_Q_RJ_final.Quarter)=',num2str(quarter_list_text(iijj+1,:)),') AND ((TP06_Q_RJ_final.Default)="Default") AND ((TP06_Q_RJ_final.[Customer Number])=[q1].[Customer Number]))');
                    curs = exec(db1_open, sqlquery3);
                    curs = fetch(curs);
                    db3_cell=curs.Data;
                    close (db1_open);
                    clear curs;
                    if strcmp(db3_cell,'No Data')
                        final_result(i,iijj,j,11) = 0;
                    else
                        default_list=cell2mat(db3_cell);
                        final_result(i,iijj,j,11)= default_list(:,1);
                        %final_result(i,iijj,j,11) = default_list(ii,2);
                    end
                    clear curs sqlquery3 db3_cell stats_list db1_open;
                    
                    final_result(i,iijj,j,12)=final_result(i,iijj,j,13)-sum(final_result(i,iijj,j,1:11));
                end
            end
        end
    end
end